/* 
 * Copyright 2014-2021 UME Framework, GNU General Public License 
 */
package com.u2framework.lib.tool.exp.table.exporter;

import java.math.BigInteger;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.u2framework.lib.core.jdbc.JdbcDao;
import com.u2framework.lib.core.jdbc.impl.JdbcDaoImpl;
import com.u2framework.lib.core.util.StringUtil;
import com.u2framework.lib.tool.exp.table.TableStructExportSQL;
import com.u2framework.lib.tool.exp.table.bean.ColumnDescBean;
import com.u2framework.lib.tool.exp.table.bean.TableStructExportBean;
import com.u2framework.lib.tool.exp.table.bean.TableStructExportParam;
import com.u2framework.lib.tool.gen.base.type.TypeMapper;

/**
 * 1.根据Excel输入的Table列表信息及数据库连接信息，查询数据字典获取表定义的结构。
 * 
 * @author SultansOfSwing & MaYue
 */
public class TableStructExporter {
    /**
     * 表字段的收取模式
     */
    public static enum ColumnExtractMode {
        /**
         * 与TableDesign格式兼容模式
         */
        TableDesignMode,
        /**
         * 只抽取有数据的字段模式
         */
        SimpleMode
    }

    /**
     * logger
     */
    protected Logger logger = LoggerFactory.getLogger(TableStructExporter.class);
    /**
     * 表结构查询SQL语句。
     */
    private String tableDescQuerySQL;
    /**
     * 数据库类型。
     */
    private String databaseType;
    /**
     * 类型映射实例。
     */
    private TypeMapper typeMapper = new TypeMapper();
    /**
     * charOfYesFlag
     */
    private String charOfYesFlag = "○";
    
    /**
     * talleStructInOnePageName
     */
    private String talleStructInOnePageName = "AllTable";
    
    /**
     * talleRecordNumInOnePageName
     */
    private String talleRecordNumInOnePageName = "AllCount";

    /**
     * 构造器。
     */
    public TableStructExporter(String databaseType) {
        this.databaseType = databaseType;
    }

    /**
     * 导出多个表的结构信息。
     *
     * @param inParamList
     * @return
     * @throws Throwable
     */
    public List<TableStructExportBean> expMultiTableInfo(List<TableStructExportParam> inParamList, DataSource ds) throws Throwable {
        if (inParamList == null) {
            return null;
        }
        List<TableStructExportBean> resultList = new ArrayList<>();
        for (TableStructExportParam inputDto : inParamList) {
            TableStructExportBean result = expTableInfo(inputDto, ds);
            resultList.add(result);
        }
        return resultList;
    }

    /**
     * 导出多个表的结构信息。
     *
     * @param inParam
     * @return
     * @throws Throwable
     */
    public TableStructExportBean expTableInfo(TableStructExportParam inParam, DataSource ds) throws Throwable {
        String schema = inParam.getSchema();
        String table = inParam.getTable();
        String tableComment = inParam.getTableComment();
        Map<String, String> param = new HashMap<String, String>();
        if (schema != null && !schema.trim().equals("") && !schema.trim().equals("*")) {
            param.put("varSchema", schema);
        }
        param.put("varTableId", table);
        logger.info("Start extract table struct of " + table);

        String sql = getTableDescQueryStr(this.databaseType);
        JdbcDao dao = createJdbcDao(ds);
        List<ColumnDescBean> columnDescList = dao.queryForObjectList(sql, param, ColumnDescBean.class);

        if (columnDescList == null || columnDescList.size() == 0) {
            throw new RuntimeException("表<" + schema + "." + table + ">不存在或表结构信息查询失败。");
        }
        for (int i = 0; i < columnDescList.size(); i++) {
            ColumnDescBean columnDesc = columnDescList.get(i);
            columnDesc.setPropertyNo(String.valueOf(i + 1));
            columnDesc.setSchema(schema);
            columnDesc.setTable(table);
            columnDesc.setTableComment(tableComment);
        }
        table = table.length() > 30 ? table.substring(0, 29) : table;
        TableStructExportBean result = new TableStructExportBean();
        result.setSchema(schema);
        result.setTable(table);
        result.setColumnDescList(columnDescList);

        logger.info("End extract table struct of " + table);
        return result;
    }

    /**
     * expMultiTableRecordNum
     *
     * @param inParamList
     * @return
     * @throws Throwable
     */
    public List<Integer> expMultiTableRecordNum(List<TableStructExportParam> inParamList, DataSource ds) throws Throwable {
        if (inParamList == null) {
            return null;
        }
        List<Integer> resultList = new ArrayList<>();
        for (TableStructExportParam inputDto : inParamList) {
            resultList.add(expTableRecordNum(inputDto, ds));
        }
        return resultList;
    }
    
    /**
     * expTableRecordNum
     * 
     * @param inParam
     * @param ds
     * @return
     * @throws Throwable
     */
    public Integer expTableRecordNum(TableStructExportParam inParam, DataSource ds) throws Throwable {
        Integer size = null;
        String schema = inParam.getSchema();
        String table = inParam.getTable();
        String countSql = "SELECT count(*) FROM " + schema + "." + table;
        JdbcDao dao = createJdbcDao(ds);
        size = dao.queryForInt(countSql, null);
        return size;
    }

    /**
     * createDataRowList
     *
     * @param table
     * @param columnDescList
     * @return
     */
    public List<List<Object>> createDataRowList(List<ColumnDescBean> columnDescList, ColumnExtractMode mode) {
        List<List<Object>> rows = new ArrayList<List<Object>>();
        for (ColumnDescBean tableDesc : columnDescList) {
            List<Object> row = createDataRow(tableDesc, mode);
            rows.add(row);
        }
        return rows;
    }

    /**
     * arrayToList
     * 
     * @param arr
     * @return
     */
    public <T> List<T> arrayToList(T[] arr) {
        List<T> list = new ArrayList<T>(arr.length);
        for (T e : arr) {
            list.add(e);
        }
        return list;
    }

    /**
     * createJdbcDao
     *
     * @param ds
     * @return
     */
    public JdbcDao createJdbcDao(DataSource ds) {
        JdbcDao dao = new JdbcDaoImpl(ds);
        return dao;
    }

    /**
     * toCamelCase
     * 
     * @param name
     * @return
     */
    public String toCamelCase(String name) {
        if (name.contains("_")) {
            String[] words = name.split("_");
            StringBuilder newName = new StringBuilder();
            for (int p = 0; p < words.length; p++) {
                if (p == 0) {
                    newName.append(words[p].toLowerCase());
                } else {
                    newName.append(words[p].toUpperCase().charAt(0) + words[p].toLowerCase().substring(1));
                }
            }
            name = newName.toString();
        } else {
            name = name.toLowerCase();
        }
        return name;
    }

    /**
     * 获取数据字典查询的SQL。
     * 
     * @return the tableDescQueryStr
     */
    protected String getTableDescQueryStr(String databaseType) {
        if (tableDescQuerySQL != null) {
            return tableDescQuerySQL;
        }
        if (databaseType.equalsIgnoreCase("ORACLE")) {
            return TableStructExportSQL.TABLE_DESC_QUERY_FOR_ORACLE;
        } else if (databaseType.equalsIgnoreCase("DB2")) {
            return TableStructExportSQL.TABLE_DESC_QUERY_FOR_DB2;
        } else {
            return TableStructExportSQL.TABLE_DESC_QUERY_FOR_MYSQL;
        }
    }

    /**
     * createDataRow
     *
     * @param tableName
     * @param colIndex
     * @param columnDesc
     * @return
     */
    public List<Object> createDataRow(ColumnDescBean columnDesc, ColumnExtractMode mode) {
        String schema = columnDesc.getSchema();
        String table = columnDesc.getTable();
        String colNo = columnDesc.getPropertyNo();
        String colId = columnDesc.getPropertyId();
        String colName = columnDesc.getPropertyName();
        String dataType = columnDesc.getDataType();
        BigInteger dataLength = columnDesc.getDataLength();
        Integer dataPrecision = columnDesc.getDataPrecision();
        Integer dataScale = columnDesc.getDataScale();
        String pkFlag = 1== columnDesc.getIsPrimaryKey() ? charOfYesFlag : "";
        String notNull = 1 == columnDesc.getIsNotNull() ? charOfYesFlag : "";
        String defaultValue = columnDesc.getDefaultValue();
        defaultValue = (defaultValue != null) ? defaultValue.trim() : "";
        colName = StringUtil.isEmpty(colName) ? "" : colName;

        String dataTypeWithLength = dataType;
        if (dataType.contains("(")) {
            dataType = dataType.substring(0, dataType.indexOf("(")).trim().toUpperCase();
        }
        dataType = getTypeMapper().getBizTypeFromDbDicType(dataType);
        String length = "";

        if (getDatabaseType().equalsIgnoreCase("ORACLE")) {
            if (dataPrecision != null && dataScale != null) {
                if (dataScale.toString().equals("0")) {
                    length = dataPrecision.toString();
                    dataType = getTypeMapper().getBizTypeFromDbDicType("INTEGER");
                } else {
                    length = dataPrecision + "," + dataScale;
                }
            } else if (dataPrecision != null && dataScale == null) {
                length = dataPrecision.toString();
                dataType = getTypeMapper().getBizTypeFromDbDicType("INTEGER");
            } else if (dataLength != null) {
                length = dataLength.toString();
            }
            dataTypeWithLength = dataTypeWithLength + "(" + length + ")";
        } else if (getDatabaseType().equalsIgnoreCase("MYSQL")) {
            if (dataTypeWithLength.contains("(") && dataTypeWithLength.contains(")")) {
                length = dataTypeWithLength.substring(dataTypeWithLength.indexOf("(") + 1, dataTypeWithLength.indexOf(")"));
            } else {
                if (dataPrecision != null && dataScale != null) {
                    length = dataPrecision + "," + dataScale;
                } else if (dataPrecision != null) {
                    length = dataPrecision.toString();
                } else if (dataLength != null) {
                    length = dataLength.toString();
                }
            }
        } else {
            if (dataPrecision != null && dataScale != null) {
                length = dataPrecision + "," + dataScale;
            } else if (dataPrecision != null) {
                length = dataPrecision.toString();
            } else if (dataLength != null) {
                length = dataLength.toString();
            }
            dataTypeWithLength = dataTypeWithLength + "(" + length + ")";
        }

        if (mode.equals(ColumnExtractMode.TableDesignMode)) {
            return arrayToList(
                    new Object[] { colNo, colName, colId, dataType, length, pkFlag, notNull, defaultValue, "", "", "", "", "", dataTypeWithLength, "" });
        } else {
            return arrayToList(
                    new Object[] { schema, table, colNo, colId, colName, dataType, length, pkFlag, notNull, defaultValue, dataTypeWithLength });
        }
    }

    /**
     * parseDatetime
     *
     * @param value
     * @param formats
     * @return
     */
    protected java.util.Date parseDatetime(String value, String[] formats) {
        java.util.Date date = null;
        for (String format : formats) {
            try {
                date = new SimpleDateFormat(format).parse(value);
            } catch (ParseException e) {
            }
        }
        return date;
    }

    /**
     * @return the databaseType
     */
    public String getDatabaseType() {
        return databaseType;
    }

    /**
     * @param databaseType the databaseType to set
     */
    public void setDatabaseType(String databaseType) {
        this.databaseType = databaseType;
    }

    /**
     * @return the typeMapper
     */
    public TypeMapper getTypeMapper() {
        return typeMapper;
    }

    /**
     * @param typeMapper the typeMapper to set
     */
    public void setTypeMapper(TypeMapper typeMapper) {
        this.typeMapper = typeMapper;
    }

    /**
     * @return the tableDescQuerySQL
     */
    public String getTableDescQuerySQL() {
        return tableDescQuerySQL;
    }

    /**
     * @param tableDescQuerySQL the tableDescQuerySQL to set
     */
    public void setTableDescQuerySQL(String tableDescQuerySQL) {
        this.tableDescQuerySQL = tableDescQuerySQL;
    }

    /**
     * getCharOfYesFlag
     * 
     * @return the charOfYesFlag
     */
    public String getCharOfYesFlag() {
        return charOfYesFlag;
    }

    /**
     * setCharOfYesFlag
     * 
     * @param charOfYesFlag the charOfYesFlag to set
     */
    public void setCharOfYesFlag(String charOfYesFlag) {
        this.charOfYesFlag = charOfYesFlag;
    }

    /**
     * getTalleStructInOnePageName
     * 
     * @return the talleStructInOnePageName
     */
    public String getTalleStructInOnePageName() {
        return talleStructInOnePageName;
    }

    /**
     * setTalleStructInOnePageName
     * 
     * @param talleStructInOnePageName the talleStructInOnePageName to set
     */
    public void setTalleStructInOnePageName(String talleStructInOnePageName) {
        this.talleStructInOnePageName = talleStructInOnePageName;
    }

    /**
     * getTalleRecordNumInOnePageName
     * 
     * @return the talleRecordNumInOnePageName
     */
    public String getTalleRecordNumInOnePageName() {
        return talleRecordNumInOnePageName;
    }

    /**
     * setTalleRecordNumInOnePageName
     * 
     * @param talleRecordNumInOnePageName the talleRecordNumInOnePageName to set
     */
    public void setTalleRecordNumInOnePageName(String talleRecordNumInOnePageName) {
        this.talleRecordNumInOnePageName = talleRecordNumInOnePageName;
    }
}
